/**
 * @author Huynh Ngoc Tin
 *
 */
package uit.tkorg.coauthorgraph.dbaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import uit.tkorg.coauthorgraph.dto.AuthorDTO;
import uit.tkorg.coauthorgraph.dto.AuthorPaperDTO;
import uit.tkorg.coauthorgraph.graph.CoAuthorGraphVertex;

public class AuthorPaperMapper extends MapperDB {

    public AuthorPaperMapper() throws Exception {
            super();
    }

    public AuthorPaperMapper(Connection con) {
            super(con);
            // TODO Auto-generated constructor stub
    }

    public boolean isAuthorPaperExist(int authorID, int paperID) throws Exception {
        boolean isExist = false;
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT * FROM coauthorgraph.authorpaper authorpaper");
            sql.append(" WHERE authorpaper.authorID = ? AND authorpaper.paperID = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            stmt.setInt(2, paperID);
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                isExist = true;
            }
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
            throw e;
        }

        return isExist;
    }

    /**
     * insert a new relation between the author and the paper
     * @param authorDTO
     * @throws Exception
     */
    public void insertNewAuthorPaper(int authorID, int paperID, int author_OrderNum) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" INSERT INTO coauthorgraph.authorpaper(authorID, paperID, author_OrderNum) ");
            sql.append(" VALUES(?, ?, ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            stmt.setInt(2, paperID);
            stmt.setInt(3, author_OrderNum);

            stmt.execute();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    /**
     * get all papers of specified author
     * @param authorID
     * @throws Exception
     */
    public ArrayList getPaperIDListOfAnAuthor(int authorID) throws Exception {
        ArrayList paperIDList = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT paperID FROM coauthorgraph.authorpaper a");
            sql.append(" WHERE a.authorID = ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            String paperID;
            while (rs != null && rs.next()) {
                paperID = String.valueOf(rs.getInt("paperID"));
                if ((paperID != null) && (!paperID.equals("")))
                    paperIDList.add(paperID);
            }
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
            throw e;
        }

        return paperIDList;
    }

    /**
     * get CoAuthorList base on Papers
     * @param paperIDList
     * @return
     */
    public ArrayList getCoAuthorDTOList(ArrayList paperIDList, int authorID) throws Exception {
        ArrayList coAuthorList = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT DISTINCT a.authorID, a.authorName ");
            sql.append(" FROM coauthorgraph.author a, coauthorgraph.authorpaper ap");
            sql.append(" WHERE a.authorID = ap.authorID AND ap.paperID IN (");
            if ((paperIDList != null) && (paperIDList.size() > 0)) {
                for (int i=0; i< paperIDList.size(); i++){
                    if (i == paperIDList.size()-1)
                        sql.append(paperIDList.get(i));
                    else {
                        sql.append(paperIDList.get(i));
                        sql.append(", ");
                    }
                }
            }
            
            sql.append(")");
            sql.append(" AND a.authorID != ?");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            AuthorDTO authorDTO;
            while (rs != null && rs.next()) {
                String coAuthorName = rs.getString("authorName");
                if ((coAuthorName != null) && (!coAuthorName.equals(""))) {
                    authorDTO = new AuthorDTO();
                    authorDTO.setAuthorID(rs.getInt("authorID"));
                    authorDTO.setAuthorName(coAuthorName);
                    coAuthorList.add(authorDTO);
                }
            }
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return coAuthorList;
    }

    /**
     * getCoAuthorList
     * @param authorID
     * @return
     * @throws Exception
     */
    public ArrayList getCoAuthorIDList(int authorID) throws Exception {
        ArrayList coAuthorIDList = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT DISTINCT a.authorID, a.authorName");
            sql.append(" FROM coauthorgraph.authorpaper ap, coauthorgraph.author a");
            sql.append(" WHERE a.authorID = ap.authorID AND ap.authorID != ?");
            sql.append(" AND ap.paperID in (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID = ?)");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            stmt.setInt(2, authorID);
            ResultSet rs = stmt.executeQuery();
            while (rs != null && rs.next()) {
                int coAuthorID = rs.getInt("authorID");
                coAuthorIDList.add(new Integer(coAuthorID));
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return coAuthorIDList;
    }

    /**
     * getAuthorPaperList
     * @param authorID
     * @return
     * @throws Exception
     */
    public ArrayList getAuthorPaperDTOList(int authorID) throws Exception {
        ArrayList authorPaperList = new ArrayList();
        try {
            Connection conn = this.getConnection();
            // get the list of publication relate to this author and add to the vertex
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT ap.paperID, ap.author_OrderNum, p.conferenceID, p.journalID");
            sql.append(" FROM coauthorgraph.authorpaper ap, coauthorgraph.paper p");
            sql.append(" WHERE ap.paperID =  p.paperID");
            sql.append(" AND ap.authorID = ?");
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            int paperID = -1;
            while (rs != null && rs.next()) {
                paperID = rs.getInt("paperID");
                int author_OrderNum = rs.getInt("author_OrderNum");
                int conferenceID = rs.getInt("conferenceID");
                int journalID = rs.getInt("journalID");
                AuthorPaperDTO authorPaperDTO = new AuthorPaperDTO();
                authorPaperDTO.setAuthorID(authorID);
                authorPaperDTO.setPaperID(paperID);
                authorPaperDTO.setAuthor_OrderNum(author_OrderNum);
                authorPaperDTO.setConferenceID(conferenceID);
                authorPaperDTO.setJournalID(journalID);
                authorPaperList.add(authorPaperDTO);
            }
        }
        catch (Exception e) {
            System.out.println(e.toString());
            throw e;
        }
        return authorPaperList;
    }

    /**
     * getTotalPaperInEachConfOfAnAuthor
     * @param authorID
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachConfOfAnAuthor(int authorID) throws Exception {
         /* Tim cac conference ma tac gia da goi bai, so bai moi confernece */
        HashMap confPaperCountMap = new HashMap();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT c.conferenceID, c.conferenceName, count(c.conferenceName) as ct");
            sql.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p");
            sql.append(" WHERE p.conferenceID = c.conferenceID AND p.paperID in ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID = ?)");
            sql.append(" GROUP BY c.conferenceName ORDER BY ct");
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            while (rs != null && rs.next()) {
                String conferenceName = rs.getString("conferenceName");
                int count = rs.getInt("ct");
                confPaperCountMap.put(conferenceName, new Integer(count));
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        return confPaperCountMap;
    }

    /**
     * getTotalPaperInEachJourOfAnAuthor
     * @param authorID
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachJourOfAnAuthor(int authorID) throws Exception {
        HashMap jourPaperCountMap = new HashMap();
        try {
            /* Tim cac journal ma tac gia da goi bai, so bai moi journal */
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT j.journalID, j.journalName, count(j.journalName) as ct");
            sql.append(" FROM coauthorgraph.journal j, coauthorgraph.paper p");
            sql.append(" WHERE p.journalID = j.journalID AND p.paperID in ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID = ?)");
            sql.append(" GROUP BY j.journalName ORDER BY ct");
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();

            while (rs != null && rs.next()) {
                String journalName = rs.getString("journalName");
                int count = rs.getInt("ct");
                jourPaperCountMap.put(journalName, new Integer(count));
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

        return jourPaperCountMap;
    }

    /**
     * getTotalPaperInEachConfOfAuthorList
     * @param authorIDList
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachConfOfAuthorList(ArrayList authorIDList) throws Exception {
        HashMap confPaperCountHashMap = new HashMap();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT c.conferenceID, c.conferenceName, count(c.conferenceName) as ct");
            sql.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p");
            sql.append(" WHERE p.conferenceID = c.conferenceID AND p.paperID IN ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
            for (int i=0; i<authorIDList.size(); i++) {
                String curAuthorID = (String) authorIDList.get(i);
                if (i == authorIDList.size()-1)
                    sql.append(curAuthorID);
                else
                    sql.append(curAuthorID + ",");
            }
            sql.append(" ))");
            sql.append(" GROUP BY c.conferenceName ORDER BY ct");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while (rs != null && rs.next()) {
                String confName = rs.getString("conferenceName");
                int count = rs.getInt("ct");
                confPaperCountHashMap.put(confName, new Double(count));
            }
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return confPaperCountHashMap;
    }

    /**
     * getTotalPaperInEachConfOfAuthorList
     * @param authorIDList
     * @param confNameList
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachConfOfAuthorList(ArrayList authorIDList, ArrayList confNameList) throws Exception {
        HashMap confPaperCountHashMap = new HashMap();
        try {
            if ((authorIDList == null) || (authorIDList.size() == 0)) {
                if (confNameList != null && confNameList.size() > 0) {
                    for (int i=0; i<confNameList.size(); i++) {
                        confPaperCountHashMap.put(confNameList.get(i), new Double(0));
                    }
                }
                return confPaperCountHashMap;
            }

            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT c.conferenceID, c.conferenceName, count(c.conferenceName) as ct");
            sql.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p");
            sql.append(" WHERE p.conferenceID = c.conferenceID AND p.paperID IN ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
            for (int i=0; i<authorIDList.size(); i++) {
                String curAuthorID = (String) authorIDList.get(i);
                if (i == authorIDList.size()-1)
                    sql.append(curAuthorID);
                else
                    sql.append(curAuthorID + ",");
            }
            sql.append(" ))");
            sql.append(" AND c.conferenceName IN (");
            for (int i=0; i<confNameList.size(); i++) {
                if (i == confNameList.size()-1)
                    sql.append("?");
                else
                    sql.append("?,");
            }
            sql.append(" )");

            sql.append(" GROUP BY c.conferenceName ORDER BY ct");
            System.out.println("........." + sql.toString());
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            for (int i=0; i<confNameList.size(); i++) {
                stmt.setString(i+1, (String) confNameList.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs != null && rs.next()) {
                String confName = rs.getString("conferenceName");
                double count = rs.getDouble("ct");
                if (confName.equalsIgnoreCase("International Conference on Hardware Software Codesign - CODES")
                        || confName.equalsIgnoreCase("International Symposium on Systems Synthesis - ISSS")
                        || confName.equalsIgnoreCase("International Conference on Hardware Software Codesign - CODES+ISSS")) {

                    Double curValue = null;
                    curValue = (Double) confPaperCountHashMap.get("International Conference on Hardware Software Codesign - CODES");
                    if (curValue == null) {
                        curValue = (Double) confPaperCountHashMap.get("International Symposium on Systems Synthesis - ISSS");
                        if (curValue == null)
                            curValue = (Double) confPaperCountHashMap.get("International Conference on Hardware Software Codesign - CODES+ISSS");
                    }
                    if (curValue != null) {
                        count += curValue.doubleValue();
                    }
                    
                    confPaperCountHashMap.put(confName, new Double(count));
                }
                else {
                    confPaperCountHashMap.put(confName, new Double(count));
                }
            }
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return confPaperCountHashMap;
    }

    /**
     * getTotalPaperInEachJourOfAuthorList
     * @param authorIDList
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachJourOfAuthorList(ArrayList authorIDList) throws Exception {
        HashMap jourPaperCountHashMap = new HashMap();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT j.journalID, j.journalName, count(j.journalName) as ct");
            sql.append(" FROM coauthorgraph.journal j, coauthorgraph.paper p");
            sql.append(" WHERE p.journalID = j.journalID AND p.paperID IN ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
            for (int i=0; i<authorIDList.size(); i++) {
                String curAuthorID = (String) authorIDList.get(i);
                if (i == authorIDList.size()-1)
                    sql.append(curAuthorID);
                else
                    sql.append(curAuthorID + ",");
            }
            sql.append(" ))");
            sql.append(" GROUP BY j.journalName ORDER BY ct");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();

            while (rs != null && rs.next()) {
                String jourName = rs.getString("journalName");
                int count = rs.getInt("ct");
                jourPaperCountHashMap.put(jourName, new Double(count));
            }
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return jourPaperCountHashMap;
    }

    /**
     * getTotalPaperInEachJourOfAuthorList
     * @param authorIDList
     * @param jourNameList
     * @return
     * @throws Exception
     */
    public HashMap getTotalPaperInEachJourOfAuthorList(ArrayList authorIDList, ArrayList jourNameList) throws Exception {
        HashMap jourPaperCountHashMap = new HashMap();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(" SELECT j.journalID, j.journalName, count(j.journalName) as ct");
            sql.append(" FROM coauthorgraph.journal j, coauthorgraph.paper p");
            sql.append(" WHERE p.journalID = j.journalID AND p.paperID IN ");
            sql.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID IN (");
            for (int i=0; i<authorIDList.size(); i++) {
                String curAuthorID = (String) authorIDList.get(i);
                if (i == authorIDList.size()-1)
                    sql.append(curAuthorID);
                else
                    sql.append(curAuthorID + ",");
            }
            sql.append(" ))");
            sql.append(" j.journalName IN (");
            for (int i=0; i<jourNameList.size(); i++) {
                if (i == jourNameList.size()-1)
                    sql.append("?");
                else
                    sql.append("?,");
            }
            sql.append(" )");

            sql.append(" GROUP BY j.journalName ORDER BY ct");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            for (int i=0; i<jourNameList.size(); i++) {
                stmt.setString(i+1, (String) jourNameList.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs != null && rs.next()) {
                String jourName = rs.getString("journalName");
                double count = rs.getDouble("ct");
                if (jourName.equalsIgnoreCase("International Conference on Hardware Software Codesign - CODES") ||
                        jourName.equalsIgnoreCase("International Symposium on Systems Synthesis - ISSS")) {

                    Double curValue = null;
                    curValue = (Double) jourPaperCountHashMap.get("International Conference on Hardware Software Codesign - CODES");
                    if (curValue == null) {
                        curValue = (Double) jourPaperCountHashMap.get("International Symposium on Systems Synthesis - ISSS");
                    }
                    if (curValue != null) {
                        count += curValue.doubleValue();
                    }

                    jourPaperCountHashMap.put(jourName, new Double(count));
                }
                else {
                    jourPaperCountHashMap.put(jourName, new Double(count));
                }
            }
            rs.close();
            stmt.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return jourPaperCountHashMap;
    }

    /**
     * Count the the total of number paper of an author in a conference
     * @param authorID
     * @param confName
     * @return
     * @throws Exception
     */
    public double countTotalPaperInConfOfAnAuthor(int authorID, String confName) throws Exception {
        double totalPaperOfAuthorInConf = 0;
        try {
            // Count the the total of number paper of an author in a conference
            StringBuffer sqlStr = new StringBuffer();

            if (confName.equalsIgnoreCase("International Conference on Hardware Software Codesign - CODES")
                        || confName.equalsIgnoreCase("International Symposium on Systems Synthesis - ISSS")
                        || confName.equalsIgnoreCase("International Conference on Hardware Software Codesign - CODES+ISSS")) {
                
                sqlStr.append(" SELECT count(*) as totalPaperOfAuthorInConf");
                sqlStr.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p, coauthorgraph.authorpaper ap");
                sqlStr.append(" WHERE p.conferenceID = c.conferenceID AND ap.paperID = p.paperID");
                sqlStr.append(" AND ap.authorID = ? AND (c.conferenceName = ? OR c.conferenceName = ? OR c.conferenceName = ?)");
                Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sqlStr.toString());
                stmt.setInt(1, authorID);
                stmt.setString(2, "International Conference on Hardware Software Codesign - CODES");
                stmt.setString(3, "International Symposium on Systems Synthesis - ISSS");
                stmt.setString(4, "International Conference on Hardware Software Codesign - CODES+ISSS");
                ResultSet rs = stmt.executeQuery();
                if (rs != null && rs.next()) {
                    totalPaperOfAuthorInConf = rs.getInt("totalPaperOfAuthorInConf");
                    rs.close();
                }
                stmt.close();

            }
            else {
                sqlStr.append(" SELECT count(*) as totalPaperOfAuthorInConf");
                sqlStr.append(" FROM coauthorgraph.conference c, coauthorgraph.paper p, coauthorgraph.authorpaper ap");
                sqlStr.append(" WHERE p.conferenceID = c.conferenceID AND ap.paperID = p.paperID");
                sqlStr.append(" AND c.conferenceName = ? AND ap.authorID = ?");
                Connection conn = getConnection();
                PreparedStatement stmt = conn.prepareStatement(sqlStr.toString());
                stmt.setString(1, confName);
                stmt.setInt(2, authorID);
                ResultSet rs = stmt.executeQuery();
                if (rs != null && rs.next()) {
                    totalPaperOfAuthorInConf = rs.getInt("totalPaperOfAuthorInConf");
                    rs.close();
                }
                stmt.close();
            }
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        return totalPaperOfAuthorInConf;
    }

    /**
     * Count the the total of number paper of an author in a journal
     * @param authorID
     * @param jourName
     * @return
     * @throws Exception
     */
    public double countTotalPaperInJourOfAnAuthor(int authorID, String jourName) throws Exception {
        double totalPaperOfAuthorInJour = 0;
        try {
            // Count the the total of number paper of an author in a journal
            StringBuffer sqlStr = new StringBuffer();
            sqlStr.append(" SELECT count(*) as totalPaperOfAuthorInJour");
            sqlStr.append(" FROM coauthorgraph.journal j, coauthorgraph.paper p, coauthorgraph.authorpaper ap");
            sqlStr.append(" WHERE p.journalID = j.journalID AND ap.paperID = p.paperID");
            sqlStr.append(" AND j.journalName = ? AND ap.authorID = ?");
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sqlStr.toString());
            stmt.setString(1, jourName);
            stmt.setInt(2, authorID);
            ResultSet rs = stmt.executeQuery();

            if (rs != null && rs.next()) {
                totalPaperOfAuthorInJour = rs.getInt("totalPaperOfAuthorInJour");
                rs.close();
            }
            stmt.close();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

        return totalPaperOfAuthorInJour;
    }

    /**
     * Count the the total of number paper of an author
     * @param authorID
     * @return
     * @throws Exception
     */
    public double countTotalPaperOfAnAuthor(int authorID) throws Exception {
        double totalPaperOfAuthor = 0;
        try {
            // Count the the total of number paper of an author
            StringBuffer sqlStr = new StringBuffer();
            sqlStr.append(" SELECT COUNT(*) as totalPaperOfAuthor");
            sqlStr.append(" FROM coauthorgraph.authorpaper ap");
            sqlStr.append(" WHERE ap.authorID = ?");
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sqlStr.toString());
            stmt.setInt(1, authorID);
            ResultSet rs = stmt.executeQuery();
            if (rs != null && rs.next()) {
                totalPaperOfAuthor = rs.getInt("totalPaperOfAuthor");
                rs.close();
            }
            stmt.close();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

        return totalPaperOfAuthor;
    }

    /**
     * countTotalPaperOfTwoAuthorIsCoAuthor
     * @param author1
     * @param author2
     * @return
     * @throws Exception
     */
    public double countTotalPaperOfTwoAuthorIsCoAuthor (int author1, int author2)  throws Exception {
        double numberOfPapersCoAuthor = 0;
        try {
            // count the number of papers which author1 & author2 is the co-author
            StringBuffer sqlStr = new StringBuffer();
            sqlStr.append(" SELECT COUNT(*) as ct FROM coauthorgraph.authorpaper ap");
            sqlStr.append(" WHERE ap.authorID = ? AND ap.paperID IN ");
            sqlStr.append(" (SELECT ap.paperID FROM coauthorgraph.authorpaper ap WHERE ap.authorID = ?)");
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sqlStr.toString());
            stmt.setInt(1, author1);
            stmt.setInt(2, author2);
            ResultSet rs = stmt.executeQuery();
            if (rs != null & rs.next()) {
                numberOfPapersCoAuthor = rs.getDouble("ct");
            }
            rs.close();
            stmt.close();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }

        return numberOfPapersCoAuthor;
    }
}
